import pymysql.cursors

connect = pymysql.Connect(
    host='localhost',
    port=3306,
    user='root',
    passwd='root',
    db='hangban',
    charset='utf8'
)
cursor = connect.cursor()


# 登录
def login():
    admin = input('输入管理员姓名: ')
    passwd = input('输入密码: ')
    if admin == 'yqx' and passwd == 'root':
        while True:
            print('\n请选择您要进行的操作')
            print('**************')
            print('1.添加航班信息')
            print('2.删除航班信息')
            print('3.修改航班信息')
            print('4.查询航班信息')
            print('5.返回')
            print('**************')
            i = int(input('输入菜单号: '))
            if i == 1:
                add_flight()
            elif i == 2:
                cancel_flight()
            elif i == 3:
                alter_flight()
            elif i == 4:
                search_flight()
            else:
                break
    else:
        print('\n用户名或密码错误，请重新登录\n')


# 添加航班信息
def add_flight():
    sql = 'insert into flight values(%s, %s, %s, %s, %s)'
    print('请输入待添加的航班信息:')
    fno = int(input('航班号:'))
    departure = input('始发地:')
    destination = input('目的地:')
    flytime = input('日期:')
    price = input('票价:')
    row = (fno, departure, destination, flytime, price)
    print("航班号:%s\t始发地:%s\t目的地:%s\t日期:%s\t票价:%s" % row)
    num = cursor.execute(sql, row)
    if num > 0:
        connect.commit()
        print('航班信息添加成功')
    else:
        print('航班信息添加失败')


# 删除航班信息
def cancel_flight():
    fno = int(input('请输入待待删航班号:'))
    num = cursor.execute('delete from flight where fno = %s', fno)
    if num > 0:
        # 提交数据修改
        connect.commit()
        print('记录删除成功！')
    else:
        print('记录删除失败！')


# 修改航班信息
def alter_flight():
    while True:
        fno = input('输入待修改航班号：')
        cursor.execute('select * from flight where fno = %s', fno)
        flight = cursor.fetchall()
        if len(flight)<=0:
            print('未查到航班号为'+fno+'的航班信息！')
        else:
            new_fno = input('输入修改后的航班号：')
            num = cursor.execute('update flight set fno = %s where fno = %s', (new_fno, fno))
            if num > 0:
                connect.commit()
                print('数据修改成功！')
                break
            else:
                print('数据修改失败！')


# 查询航班信息
def search_flight():
    cursor.execute('select * from flight')
    connect.commit()
    print('  -------------------------航班信息-------------------------')
    for row in cursor.fetchall():
        print("航班号:%s\t始发地:%s\t目的地:%s\t日期:%s\t票价:%d" % row)


# 主程序
while True:
    print('欢迎使用航班管理系统')
    print('****************')
    print('1.登录系统')
    print('2.退出系统')
    print('****************')
    j = int(input('请选择您要进行的操作(1或2):'))
    if j == 1:
        login()
    elif j == 2:
        print('\n谢谢使用本程序\n')
        break
